set doc off
set verify off
--
--    Program: CAPGLEXT_SOB.sql
--    Description:
--       Extract gl set of books information
--
--    Revision:
--       1st
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);

   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);

   l_ngo_sob				varchar2(20);

   l_coa					fnd_id_flex_structures.id_flex_structure_code%type;
   l_created_by				fnd_user.user_name%type;
   l_last_updated_by		fnd_user.user_name%type;

   cursor cur_sob (p_sob varchar2) is
      select * from gl_sets_of_books
      where upper(short_name) = upper(p_sob);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPGL_SOB.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');

   l_processed_count := 0;
   l_rejected_count  := 0;

   l_ngo_sob := '&1';

   for cur in cur_sob(l_ngo_sob) loop

     -- output data
     utl_file.put_line( l_utl_file,
	   cur.set_of_books_id|| chr(9) ||
	   cur.name|| chr(9) ||
	   cur.short_name|| chr(9) ||
	   		cur.chart_of_accounts_id|| chr(9) ||
	   cur.currency_code|| chr(9) ||
	   cur.period_set_name|| chr(9) ||
	   cur.accounted_period_type|| chr(9) ||
	   cur.suspense_allowed_flag|| chr(9) ||
	   cur.allow_intercompany_post_flag|| chr(9) ||
	   cur.track_rounding_imbalance_flag|| chr(9) ||
	   cur.enable_average_balances_flag|| chr(9) ||
	   cur.enable_budgetary_control_flag|| chr(9) ||
	   cur.require_budget_journals_flag|| chr(9) ||
	   cur.enable_je_approval_flag|| chr(9) ||
	   cur.enable_automatic_tax_flag|| chr(9) ||
	   cur.consolidation_sob_flag|| chr(9) ||
	   cur.translate_eod_flag|| chr(9) ||
	   cur.translate_qatd_flag|| chr(9) ||
	   cur.translate_yatd_flag|| chr(9) ||
	   cur.mrc_sob_type_code|| chr(9) ||
	   cur.allow_posting_warning_flag|| chr(9) ||
	   to_char(cur.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
	   		cur.last_updated_by|| chr(9) ||
	   to_char(cur.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
	   		cur.created_by|| chr(9) ||
	   cur.last_update_login|| chr(9) ||
	   cur.future_enterable_periods_limit|| chr(9) ||
	   cur.latest_opened_period_name|| chr(9) ||
	   cur.latest_encumbrance_year|| chr(9) ||
	   		cur.ret_earn_code_combination_id|| chr(9) ||
	   		cur.cum_trans_code_combination_id|| chr(9) ||
	   		cur.res_encumb_code_combination_id|| chr(9) ||
	   		cur.net_income_code_combination_id|| chr(9) ||
	   		cur.rounding_code_combination_id|| chr(9) ||
	   cur.transaction_calendar_id|| chr(9) ||
	   cur.daily_translation_rate_type|| chr(9) ||
	   cur.earliest_untrans_period_name|| chr(9) ||
	   cur.description|| chr(9) ||
	   cur.attribute1|| chr(9) ||
	   cur.attribute2|| chr(9) ||
	   cur.attribute3|| chr(9) ||
	   cur.attribute4|| chr(9) ||
	   cur.attribute5|| chr(9) ||
	   cur.attribute6|| chr(9) ||
	   cur.attribute7|| chr(9) ||
	   cur.attribute8|| chr(9) ||
	   cur.attribute9|| chr(9) ||
	   cur.attribute10|| chr(9) ||
	   cur.attribute11|| chr(9) ||
	   cur.attribute12|| chr(9) ||
	   cur.attribute13|| chr(9) ||
	   cur.attribute14|| chr(9) ||
	   cur.attribute15|| chr(9) ||
	   cur.context|| chr(9) ||
	   cur.global_attribute_category|| chr(9) ||
	   cur.global_attribute1|| chr(9) ||
	   cur.global_attribute2|| chr(9) ||
	   cur.global_attribute3|| chr(9) ||
	   cur.global_attribute4|| chr(9) ||
	   cur.global_attribute5|| chr(9) ||
	   cur.global_attribute6|| chr(9) ||
	   cur.global_attribute7|| chr(9) ||
	   cur.global_attribute8|| chr(9) ||
	   cur.global_attribute9|| chr(9) ||
	   cur.global_attribute10|| chr(9) ||
	   cur.track_entered_func_curr_flag|| chr(9) ||
	   CAPBREXT_COMMON.GetUser(cur.LAST_UPDATED_BY)|| CHR(9) ||
	   CAPBREXT_COMMON.GetUser(cur.CREATED_BY)|| chr(9) ||
	   CAPBREXT_COMMON.GetFlexStrCode(cur.chart_of_accounts_id)|| chr(9) ||
	   CAPBREXT_COMMON.GetSegment(cur.ret_earn_code_combination_id)|| chr(9) ||
	   CAPBREXT_COMMON.GetSegment(cur.cum_trans_code_combination_id)|| chr(9) ||
	   CAPBREXT_COMMON.GetSegment(cur.res_encumb_code_combination_id)|| chr(9) ||
	   CAPBREXT_COMMON.GetSegment(cur.net_income_code_combination_id)|| chr(9) ||
	   CAPBREXT_COMMON.GetSegment(cur.rounding_code_combination_id)
	 );

	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': set of books not found';
   end if;

   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg)
    values ('CAPGLEXT_SOB', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );

   commit;

   utl_file.fclose(l_utl_file);
exception
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/
--exit
